package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.AccountHasInterestRateDao;
import csc.fresher.entity.AccountHasInterestRate;
import csc.fresher.entity.InterestRate;
import csc.fresher.entity.SavingAccountHasInterestRate;
import csc.fresher.entity.SavingInterestRate;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingAccountHasInterestRateDaoImpl implements
		AccountHasInterestRateDao {

	private static final Logger logger = Logger
			.getLogger(PeriodTypeDaoImpl.class);

	@Autowired
	DataSource dataSource;

	@Override
	public void insert(AccountHasInterestRate accountHasInterestRate) {
		String query = "INSERT INTO `SavingAccount_has_InterestRate`(`SavingAccount_AccNumber`,`InterestRate_InterestRateID`,`FromDate`, `ToDate`, `Balance`, `State`) VALUES (?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { accountHasInterestRate.getAccountNumber(), accountHasInterestRate.getInterestRateID(), accountHasInterestRate.getFromDate(), accountHasInterestRate.getToDate(), accountHasInterestRate.getBalance(), accountHasInterestRate.getState().getState() });
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void update(AccountHasInterestRate accountHasInterestRate) {
		String query = "UPDATE `SavingAccount_has_InterestRate` SET `SavingAccount_has_InterestRate`.`FromDate` = ?, SET `SavingAccount_has_InterestRate`.`ToDate`=?, SET `SavingAccount_has_InterestRate`.`Balance`=? WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber` = ? `SavingAccount_has_InterestRate`.`InterestRate_InterestRateID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { accountHasInterestRate.getFromDate(), accountHasInterestRate.getToDate(), accountHasInterestRate.getBalance(), accountHasInterestRate.getAccountNumber(), accountHasInterestRate.getInterestRateID()});
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	//not completed
	@Override
	public AccountHasInterestRate getAccountHasInterestRate(long accountNumberID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<AccountHasInterestRate> getAllAccountHasInterestRates(
			long accountNumberID) {
		List<AccountHasInterestRate> accountHasInterestRates = null;
		AccountHasInterestRate accountHasInterestRate = null;
		String query = "SELECT * FROM `SavingAccount_has_InterestRate` WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[]{accountNumberID});
			accountHasInterestRates = new ArrayList<>();
			while(rs.next()){
				accountHasInterestRate = new SavingAccountHasInterestRate();
				accountHasInterestRate.setAccountNumber(rs.getLong("SavingAccount_AccNumber"));
				accountHasInterestRate.setInterestRateID(rs.getInt("InterestRate_InterestRateID"));
				accountHasInterestRate.setFromDate(rs.getTimestamp("FromDate"));
				accountHasInterestRate.setBalance(rs.getDouble("Balance"));
				accountHasInterestRate.setToDate(rs.getTimestamp("ToDate"));
				accountHasInterestRates.add(accountHasInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return accountHasInterestRates;
	}

	
	@Override
	public List<AccountHasInterestRate> getListAccountHasInterestRates(
			int startIndex, int maxReturn) {
		List<AccountHasInterestRate> accountHasInterestRates = null;
		/*AccountHasInterestRate accountHasInterestRate = null;
		String query = "SELECT * FROM `SavingAccount_has_InterestRate` WHERE `SavingAccount_has_InterestRate`.`SavingAccount_AccNumber`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[]{startIndex, maxReturn});
			accountHasInterestRates = new ArrayList<>();
			while(rs.next()){
				accountHasInterestRate = new SavingAccountHasInterestRate();
				accountHasInterestRate.setAccountNumber(rs.getInt("SavingAccount_AccNumber"));
				accountHasInterestRate.setInterestRateID(rs.getInt("InterestRate_InterestRateID"));
				accountHasInterestRate.setFromDate(rs.getDate("FromDate"));
				accountHasInterestRate.setBalance(rs.getFloat("Balance"));
				accountHasInterestRate.setToDate(rs.getDate("ToDate"));
				accountHasInterestRates.add(accountHasInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}*/
		return accountHasInterestRates;
	}

	@Override
	public int totalAccountHasInterestRates(int accountNumberID) {
		return getAllAccountHasInterestRates(accountNumberID).size();
	}

}
